package com.owen.he.jee.utils.excel;

import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dom4j.Node;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

import com.google.gson.Gson;

/**
 * @author 周艳
 * @Date 2016-09-26
 */
public class ExportingExcel {

    private static final Logger logger = Logger.getLogger(ExportingExcel.class);
    private static HSSFWorkbook workbook;
    public static final int MAX_ROW_PER_SHEET = 65536;

    private String fileName;
    private String dateFormat = "yyyyMMdd";

    private HSSFCellStyle defaultStyle;
    private int sheetMark = 0;

    private List<Map> excelData;
    private String tempPath;
    private Integer extendRow=1; //表头有几行；
    private XmlDao xmlDao;
    private List<ExcelColumnPojo> headerColList = new ArrayList<ExcelColumnPojo>();
    
    private Integer type = 0;
    
    

    public Integer getType() {
		return type;
	}

	public void setType(Integer type) {
		this.type = type;
	}

	public List<ExcelColumnPojo> getHeaderColList() {
		return headerColList;
	}

	public void setHeaderColList(List<ExcelColumnPojo> headerColList) {
		this.headerColList = headerColList;
	}

	public ExportingExcel(){}

    public void setWorkbook(HSSFWorkbook workbook){
        this.workbook = workbook;
    }

    public String getFileName(){
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public void setExcelData(List<Map> excelData) {
        this.excelData = excelData;
    }

    public String getTempPath() {
		return tempPath;
	}

	public void setTempPath(String tempPath) {
		this.tempPath = tempPath;
	}

	public Integer getExtendRow() {
		return extendRow;
	}

	public void setExtendRow(Integer extendRow) {
		this.extendRow = extendRow;
	}
	

	public XmlDao getXmlDao() {
		return xmlDao;
	}

	public void setXmlDao(XmlDao xmlDao) {
		this.xmlDao = xmlDao;
	}
	
	/**
     * Generates the excel sheet data by HSSFWorkbook object workbook
     * @param isFirst
     * @return
     */
    @SuppressWarnings("deprecation")
	public void generateData(boolean isFirst){
    	
        if(xmlDao==null){
        	logger.info("---------excel generate data test-----------");
        	if(type == 1){
        		
        	}else{
            	return;
        	}
        	
        }else{
        	buildExcelColumnPojo();
        }
        logger.info("---------excel generate data-----------");
//        int totalSize = excelData.size();
//        int maxSizePerSheet = isFirst ? (MAX_ROW_PER_SHEET ): MAX_ROW_PER_SHEET;
//        int remainder = totalSize % maxSizePerSheet;
//        int sheetNum = remainder == 0 ? totalSize / maxSizePerSheet: totalSize / maxSizePerSheet + 1;

        
        try{
            HSSFSheet sheet = workbook.createSheet(fileName);
	        //设置表头
	        HSSFRow row1 = sheet.createRow((int) 0);    
	        HSSFRow row2 = sheet.createRow((int) 1);    
	        HSSFCellStyle style =null;
	        HSSFDataFormat format = workbook.createDataFormat();
	        for(int i=0;i<headerColList.size();i++) {
	        	style = workbook.createCellStyle(); // 样式对象    
	        	style.setDataFormat(format.getFormat("@"));  //设置为文本格式；
	            HSSFFont font=workbook.createFont();
	            font.setFontHeightInPoints((short)12);
	            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
	            //把字体应用到当前的样式
	            style.setFont(font);
		        
				ExcelColumnPojo pojo = headerColList.get(i);
				if(pojo.getColspan().equals("1")){
					setReqBackground(style, pojo.getReq());//设置必填
					short align=getAlignment(pojo.getAlign());//对齐方式
					style.setAlignment(align);
					//单列(index,0)
					if(extendRow>1){
						//有两层表头；
						// 单元格合并    
		              //sheet.addMergedRegion(new Region(0,(short)pojo.getIndex(), 1, (short)pojo.getIndex()));  
						// 四个参数分别是：起始行，起始列，结束行，结束列    
		              sheet.addMergedRegion(new CellRangeAddress(0,(short)pojo.getIndex(), 1, (short)pojo.getIndex()));
		              HSSFCell cell2 = row2.createCell(pojo.getIndex());
		              HSSFCell cell = row1.createCell(pojo.getIndex());
		              setCellFormat(sheet, style, pojo.getIndex(), pojo, cell2);
		              setCellFormat(sheet, style, pojo.getIndex(), pojo, cell);
					}else{
						 HSSFCell cell = row1.createCell(pojo.getIndex());
						 if(type == 1){
							 setCellFormat2(sheet, style, pojo.getIndex(), pojo, cell);
						 }else{
							 setCellFormat(sheet, style, pojo.getIndex(), pojo, cell);
						 }
						 
						 setBorder(style);//设置边框
					}
				}else{
					//合并列的情况
					//sheet.addMergedRegion(new Region(0,(short)pojo.getIndex(), 0, (short)(pojo.getIndex()+Integer.parseInt(pojo.getColspan())-1)));
					sheet.addMergedRegion(new CellRangeAddress(0,(short)pojo.getIndex(), 0, (short)(pojo.getIndex()+Integer.parseInt(pojo.getColspan())-1)));
					HSSFCell cell = row1.createCell(pojo.getIndex());   
					cell.setCellValue(pojo.getText());
					cell.setCellStyle(style); // 样式
					style.setAlignment(getAlignment(pojo.getAlign()));
					setBorder(style);//设置边框
					List<ExcelColumnPojo> childPojo=pojo.getChildPojo();
					for (int j = 0; j < childPojo.size(); j++) {
						HSSFCellStyle childStyle = workbook.createCellStyle(); // 样式对象    
						childStyle.setDataFormat(format.getFormat("@"));  //设置为文本格式；
			            //把字体应用到当前的样式
			            childStyle.setFont(font);
						ExcelColumnPojo child=childPojo.get(j);
						setReqBackground(childStyle, child.getReq());//设置必填
						short align=getAlignment(child.getAlign());//对齐方式
						childStyle.setAlignment(align);
						HSSFCell row2Cell = row2.createCell(child.getIndex());
						setCellFormat(sheet, childStyle, child.getIndex(), child, row2Cell);
						setBorder(childStyle);//设置边框
					}
				}
			}
	        //设置数据；
	        if(null !=excelData  && excelData.size() > 0 && type == 0){
	        	exportExcelData(sheet, format);
	        }
	        if(null !=excelData  && excelData.size() > 0 && type == 1){
	        	logger.info("generate date ing");
	        	Gson gson = new Gson();
	        	logger.info(gson.toJson(excelData));
	        	exportExcelData(sheet);
	        }
	        
        } catch (Exception e){
            logger.error("generate excel with an error", e);
        }
    }
    
    /**
     * 设置边框
     * @param style
     */
	private void setBorder(HSSFCellStyle style) {
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	}
    /**
     * 设置必填背景颜色
     * @param style
     * @param req
     */
	private void setReqBackground(HSSFCellStyle style, String req) {
		//System.out.println(req);
		if(!"".equals(req)&&"Y".equals(req)){
			style.setFillForegroundColor(IndexedColors.RED.getIndex());
		}else{
			style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		}
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	}
	
	private void exportExcelData(HSSFSheet sheet){
		int rowIndex = extendRow;
		//logger.info("rowindex:"+rowIndex);
		for(int i = 0; i < excelData.size(); i++){
			//logger.info("i:"+i);
			HSSFRow row = sheet.createRow((int) rowIndex+i);
			Map map = (Map) excelData.get(i);
			Gson gson = new Gson();
			//logger.info(gson.toJson(map));
			for(int j=0;j<headerColList.size();j++){
				HSSFCell cell = row.createCell(j);
				
					setCellValue(cell, map.get(j+"")) ;
				
				
			}
		}
	}
	
    /**
     *  填充数据
     * @param sheet
     * @param alignMap
     * @param extendRow2
     */
    private void exportExcelData(HSSFSheet sheet,HSSFDataFormat format) {
    	int rowIndex = extendRow;
    	HSSFCellStyle style=null;
    	style = workbook.createCellStyle(); // 样式对象    
    	style.setDataFormat(format.getFormat("@"));  //设置为文本格式；
        HSSFFont font=workbook.createFont();
        font.setFontHeightInPoints((short)12);
        //把字体应用到当前的样式
        style.setFont(font);
        setBorder(style);
    	for(int i = 0; i < excelData.size(); i++){
    		HSSFRow row = sheet.createRow((int) rowIndex+i);
			Map map = (Map) excelData.get(i);
			for (int j = 0; j < headerColList.size(); j++) {
				ExcelColumnPojo columns=headerColList.get(j);
				style.setAlignment(getAlignment(columns.getAlign()));
				String text="";
				if(map.containsKey(columns.getName().toUpperCase())){
					text=map.get(columns.getName().toUpperCase()).toString();
				}else{
					int col=0;
					for(Iterator iter = map.entrySet().iterator(); iter.hasNext(); ){
						Map.Entry en = (Entry) iter.next();
						if(col == columns.getIndex()){
							text=(String) en.getValue();
							break;
						}
						col++;
					}
				}
				HSSFCell cell = row.createCell(columns.getIndex());   
				cell.setCellValue(text);
				cell.setCellStyle(style); // 样式
			}
			
		}
	}

	/**
     * 设置单元格格式；
     * @param sheet
     * @param style
     * @param cellIndex
     * @param pojo
     * @param cell
     */
	private void setCellFormat(HSSFSheet sheet, HSSFCellStyle style,
			int cellIndex, ExcelColumnPojo pojo, HSSFCell cell) {
		cell.setCellValue(pojo.getText());
		cell.setCellStyle(style); // 样式
		int intColWidth = 0;
		if (pojo.getWidth() == null || pojo.getWidth().equals(""))
			intColWidth = 80 / 6;
		else
		  intColWidth = Integer.valueOf(pojo.getWidth()).intValue();
		sheet.setColumnWidth(cellIndex, 44 * intColWidth);//列宽
		//批注
		if(StringUtils.isNotBlank(pojo.getAnnotate())){
			HSSFPatriarch p=sheet.createDrawingPatriarch(); 
			HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
			comment.setString(new HSSFRichTextString(pojo.getAnnotate())); 
			cell.setCellComment(comment); 
		}
		setBorder(style);//设置边框
	}
	
	private void setCellFormat2(HSSFSheet sheet, HSSFCellStyle style,
			int cellIndex, ExcelColumnPojo pojo, HSSFCell cell) {
		cell.setCellValue(pojo.getText());
		//System.out.println(pojo.getText());
		
		int fontWidth = pojo.getText().length() * 600;
		if (fontWidth <= 2400) {
			fontWidth += 1000;
		} else if (fontWidth >= 6000) {
			fontWidth -= 1000;
		} else {
			fontWidth += 500;
		}
		if (cellIndex == 0) {
			fontWidth = 8000;
		}
		sheet.setColumnWidth(cellIndex, fontWidth);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cell.setCellStyle(style);
		setBorder(style);//设置边框
	}
   /**
    * 对齐方式
    * @return
    */
    private short getAlignment(String align) {
    	if(StringUtils.isNotBlank(align)){
    		if(align.equalsIgnoreCase("center")){
    			return HSSFCellStyle.ALIGN_CENTER;
    		}else if(align.equalsIgnoreCase("left")){
    			return HSSFCellStyle.ALIGN_LEFT;
    		}else if(align.equalsIgnoreCase("right")){
    			return HSSFCellStyle.ALIGN_RIGHT;
    		}else if(align.equalsIgnoreCase("general")){
    			return HSSFCellStyle.ALIGN_GENERAL;
    		}else if(align.equalsIgnoreCase("fill")){
    			return HSSFCellStyle.ALIGN_FILL;
    		}else if(align.equalsIgnoreCase("justify")){
    			return HSSFCellStyle.ALIGN_JUSTIFY;
    		}
    	}
		return HSSFCellStyle.ALIGN_CENTER;
	}

	/**
     * 根据xmlDao解析出相应的值，生成对象
     */
	private void buildExcelColumnPojo() {
    	List<Node> colList = xmlDao.getNodes("pageSetting/template/column");
    	Map<String,ExcelColumnPojo> columList=new HashMap<String,ExcelColumnPojo>();
    	for (int i = 0; i < colList.size(); i++) {
    		ExcelColumnPojo pojo=new ExcelColumnPojo();
    		Node colNode = colList.get(i);
    		int index = Integer.parseInt(xmlDao.getValue(colNode, "index"));
    		pojo.setIndex(index);
    		String name=xmlDao.getValue(colNode, "name");
    		pojo.setName(name);
    		// 设置水平对齐方式
    		pojo.setAlign(xmlDao.getValue(colNode, "align"));
    		pojo.setReq(xmlDao.getValue(colNode, "req"));
    		pojo.setText(xmlDao.getValue(colNode, "text"));
    		pojo.setAnnotate(xmlDao.getValue(colNode, "annotate"));
    		pojo.setWidth(xmlDao.getValue(colNode, "width"));
    		columList.put(name, pojo);
    	}
		if(extendRow>1){
			String tableDesc = xmlDao.getPartXml("pageSetting/table");
			Reader in1 = new StringReader(tableDesc);
			Document tableDoc =null;
			try {
				tableDoc = (new SAXBuilder()).build(in1);
			} catch (JDOMException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			int index = 0;
			List<Element> tbHeads = tableDoc.getRootElement().getChildren();
			//此for循环是用来设置colspan及index,index是用来和pagesetting/template/column相匹配的
			for (Element e : tbHeads) {
				if ("ZT".equalsIgnoreCase(e.getAttributeValue("id"))
						|| "ERROR".equalsIgnoreCase(e.getAttributeValue("id"))) {
					continue;
				}
				String pojoId=e.getAttributeValue("id").toLowerCase();
				ExcelColumnPojo pojo=new ExcelColumnPojo();
				if(columList.containsKey(pojoId)){
					pojo=columList.get(pojoId);
				}else{
					pojo.setName(pojoId);
					pojo.setAlign(e.getAttributeValue("align").toString());
					pojo.setText(e.getAttributeValue("title").toString());
					pojo.setWidth(e.getAttributeValue("width").toString());
				}
				if (e.getChildren().size() > 0) {
					List<Element> children = e.getChildren();
					pojo.setColspan(String.valueOf(e.getChildren().size()));
					pojo.setIndex(index);
					List<ExcelColumnPojo> childPojoList=new ArrayList<ExcelColumnPojo>();
					for (Element child : children) {
						String chileId=child.getAttributeValue("id").toLowerCase();
						ExcelColumnPojo childPojo=columList.get(chileId);
						childPojo.setIndex(index);
						childPojoList.add(childPojo);
						index++;
					}
					pojo.setChildPojo(childPojoList);
				} else {
					pojo.setColspan("1");
					pojo.setIndex(index);
					index++;
				}
				this.headerColList.add(pojo);
			}
		}else{
			for (Iterator iterator = columList.values().iterator(); iterator.hasNext();) {
				ExcelColumnPojo headerCol = (ExcelColumnPojo) iterator.next();
				this.headerColList.add(headerCol);
			}
		}
	}

	/**
     */
    public void generateCell(List<Map> data, HSSFRow row,
                             int columns){
        for(int j = 0; j < columns ; j++){
            HSSFCell cell = row.createCell(j);
            logger.info("i="+j+",value="+data.get(0).get(j));
            setCellValue(cell, data.get(0).get(j)) ;
        }
    }
    /**
     * Builds the excel cell with the specified cell value.
     * @param cell   cell object
     * @param value  specified value object
     */

    public void setCellValue(HSSFCell cell, Object value) {
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        }else if (value instanceof String) {
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString((String) value));
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
            cell.setCellStyle(getDefaultStyle());
        } else if (value instanceof BigDecimal){
            cell.setCellValue(((BigDecimal)value).doubleValue());
        } else if(value instanceof BigInteger){
            cell.setCellValue(((BigInteger)value).doubleValue());
        }  else if(value instanceof Long){
            cell.setCellValue(((Long)value).longValue());
        } else{
            logger.info("can't set CellValue with " + value.getClass() + " class type");
        }
    }

    public HSSFCellStyle getDefaultStyle() {
        if(defaultStyle==null){
            defaultStyle = workbook.createCellStyle();
            defaultStyle.setWrapText(true);
            defaultStyle.setDataFormat(workbook.createDataFormat().getFormat(dateFormat));
        }
        return defaultStyle;
    }

    public void setDateFormat(String format){
        dateFormat = format ;
    }
    
    
    
}